using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for AdminClass
/// </summary>
namespace QnA
{

    public class AdminClass
    {
        public static SqlConnection connection;
        public static DataSet dataset;
        
        public AdminClass()
        {
            //
            // TODO: Add constructor logic here//

            if (connection == null)
            {
                try
                {
                    String conn = System.Configuration.ConfigurationManager.AppSettings["conn"];
                    connection = new SqlConnection(conn);
                    if (dataset == null) dataset = new DataSet();
                }
                catch (Exception se)
                {
                    HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=khong connect duoc");
                    
                }
            }
        }

        public void adminAuthenticate()
        {
            if (HttpContext.Current.Session["admin"] == null) HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=1");
            if (HttpContext.Current.Session["admin"].ToString().Equals("") )
            {
                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=1");
            }

            if (!HttpContext.Current.Session["admin"].ToString().Equals("admin"))
            {
                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=1");
            }


        }


        #region HoaHong
        /**Hoa Hong**/

        public void selectCommision()
        {
            try
            {
                if (dataset.Tables["Admin"] != null) dataset.Tables.Remove("Admin"); ;
               
                SqlDataAdapter adapter = new SqlDataAdapter("select * from Admin", connection);
                adapter.Fill(dataset, "Admin");
            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=select comminsion");
            }

        }


        public Boolean updateCommision(int value)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "Update Admin set Commission=@com";

                cmd.Parameters.Add("@com",SqlDbType.SmallInt,3).Value = value;
                
               
               
                int k=cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                if (k ==1) return true;
                else return false;
            }
            catch (Exception)
            {

                return false;
            }
        }


        /**Hoa Hong**/
        #endregion HoaHong

        #region News
        public void selectNews()
        {
            try
            {
                if (dataset.Tables["News"] != null)
                {
                    dataset.Tables.Remove("News");
                }
                SqlDataAdapter adapter = new SqlDataAdapter("select * from News", connection);
                adapter.Fill(dataset, "News");
            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=selectnews");
            }

        }

        public bool addNews(String newstitle, String newscontent)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"INSERT INTO [Tuvan].[dbo].[News]
           ([NewsTitle]
           ,[NewsContent]
           ,[NewsTime])
     VALUES
           (@title,@content,@time)";

                cmd.Parameters.Add("@title",SqlDbType.NVarChar,50).Value = newstitle;
           /*co the chinh so luong cho content*/
                cmd.Parameters.Add("@content", SqlDbType.NVarChar).Value = newscontent;
                cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = DateTime.Now.ToString();




                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                if (k == 1) return true;
                else return false;
            }
            catch (Exception adde)
            {

                return false;
            }
        
        }

        public bool deleteNews(int newsid)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"DELETE FROM [Tuvan].[dbo].[News]
      WHERE NewsID=@ID";

                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newsid;
                /*co the chinh so luong cho content*/
               



                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                return true;
                
               
            }
            catch (Exception deletee)
            {

                
                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=deletenews");
                return false;

            }
        
        }


        public bool updateNews(int newsid,String newstitle,String newscontent,DateTime time)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"UPDATE [Tuvan].[dbo].[News]
   SET [NewsTitle] = @title
      ,[NewsContent] = @content
      ,[NewsTime] = @time
 WHERE NewsID=@ID";

                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newsid;
                cmd.Parameters.Add("@title", SqlDbType.NVarChar, 50).Value = newstitle;
                /*co the chinh so luong cho content*/
                cmd.Parameters.Add("@content", SqlDbType.NVarChar).Value = newscontent;
                cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = time;
                /*co the chinh so luong cho content*/




                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                if (k == 1) return true;
                else return false;
            }
            catch (Exception)
            {

                return false;
            }

        }

        #endregion News

        #region Account
        public void selectAccount()
        {
            try
            {
                if (dataset.Tables["Account"] != null) dataset.Tables.Remove("Account");

                SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT     dbo.Member.*, dbo.Member.Role AS Expr1
FROM        dbo.Member
WHERE     (dbo.Member.Role = 2)", connection);
                adapter.Fill(dataset, "Account");
            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=selectaccount");
            }
        }

        public bool updateAccount(int id,decimal currentmoney,decimal rutmoney,String comment)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"UPDATE [Tuvan].[dbo].[Member]
   SET 
      [Account] = @money
     
 WHERE UserID=@id";

                decimal a = currentmoney - rutmoney;
                cmd.Parameters.Add("@money", SqlDbType.Money).Value = a;
                cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;


                int k = cmd.ExecuteNonQuery();
                cmd.Dispose();
              

                SqlCommand cmd1 = connection.CreateCommand();
                cmd1.CommandText = @"INSERT INTO [Tuvan].[dbo].[Transaction]
           (
           [UserID]
           ,[TransAmount]
           ,[TransDate]
           ,[TransNote])
     VALUES
           (
           @userid
           ,@transamout
           ,@transdate
           ,@transnote)";
                cmd1.Parameters.Add("@userid", SqlDbType.Int).Value = id;
                cmd1.Parameters.Add("@transamout", SqlDbType.Money).Value = rutmoney;
                cmd1.Parameters.Add("@transdate", SqlDbType.DateTime).Value = DateTime.Now;
                cmd1.Parameters.Add("@transnote", SqlDbType.VarChar,50).Value = comment;

                int k1 = cmd1.ExecuteNonQuery();


                
                
                connection.Close();
                cmd1.Dispose();
                 return true;
               
            }
            catch (Exception updateA)
            {

                throw new Exception(updateA.Message);
                return false;
            }
        }

        #endregion Account


        #region Session
        public void selectSession()
        {
            try
            {
                if (dataset.Tables["Session"] != null) dataset.Tables.Remove("Session");
                if (dataset.Tables["Member"] != null) dataset.Tables.Remove("Member");

                SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT     dbo.Session.*
FROM         dbo.Session
WHERE     (SessionPayment <> 0)", connection);
                adapter.Fill(dataset, "Session");
                
                SqlDataAdapter adapter1=new SqlDataAdapter(@"SELECT dbo.Member.* FROM  dbo.Member",connection);
                adapter1.Fill(dataset, "Member");
            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=admin"+e.Message);
            }
        }
        
    #endregion Session


        #region Comment
        public void selectComment()
        {
            try
            {
                if (dataset.Tables["Comment"] != null) dataset.Tables.Remove("Comment");
                if (dataset.Tables["Member"] != null) dataset.Tables.Remove("Member");

                SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT [CommentID]
      ,[CommentContent]
      ,[CommentDate]
      ,[Rating]
      ,[FromID]
      ,[ToID]
  FROM [Tuvan].[dbo].[Comment]", connection);
                adapter.Fill(dataset, "Comment");
                SqlDataAdapter adapter1 = new SqlDataAdapter(@"SELECT dbo.Member.* FROM  dbo.Member", connection);
                adapter1.Fill(dataset, "Member");

            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=admin" + e.Message);
            }
        }



        public void deleteComment(int commenid)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"DELETE FROM [Tuvan].[dbo].[Comment]
      WHERE CommentID=@ID";

                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = commenid;
                /*co the chinh so luong cho content*/




                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
               

            }
            catch (Exception deletee)
            {

                throw deletee;
                
                
            }
        
        }
    

        #endregion Comment


        #region category

        public void selectCategory()
        {
            try
            {
                if (dataset.Tables["Category"] != null) dataset.Tables.Remove("Category");


                SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT [CatID]
      ,[CatName]
      ,[TotalExpert]
      ,[ParentID]
  FROM [Tuvan].[dbo].[Category]", connection);
                adapter.Fill(dataset, "Category");
              

            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=admin" + e.Message);
            }
        }


        public bool addCategory(String catname,int parentid)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"INSERT INTO [Tuvan].[dbo].[Category]
           ([CatName]
           ,[TotalExpert]
           ,[ParentID])
     VALUES
           (@catname
           ,0
           ,@parentid)";

                cmd.Parameters.Add("@catname", SqlDbType.NVarChar, 50).Value = catname;
                /*co the chinh so luong cho content*/
                cmd.Parameters.Add("@parentid", SqlDbType.SmallInt).Value = parentid;
               // cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = DateTime.Now.ToString();




                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                if (k == 1) return true;
                else return false;
            }
            catch (Exception adde)
            {

                return false;
            }

        }


        public bool deleteCategory(int catid)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"DELETE FROM [Tuvan].[dbo].[Category]
      WHERE CatID=@ID";

                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = catid;
                /*co the chinh so luong cho content*/




                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                return true;


            }
            catch (Exception deletee)
            {


               
                return false;

            }

        }

        public Boolean updateCategory(int catid, String catname,int parentid)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"UPDATE [Tuvan].[dbo].[Category]
 SET 
[CatName]=@catname,      
[ParentID] = @parentid
     
 WHERE CatID=@catid";

                cmd.Parameters.Add("@catid", SqlDbType.SmallInt).Value = catid;
                cmd.Parameters.Add("@catname", SqlDbType.NVarChar, 50).Value = catname;
                /*co the chinh so luong cho content*/
                cmd.Parameters.Add("@parentid", SqlDbType.SmallInt).Value = parentid;
                // cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = DateTime.Now.ToString();


                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                if (k == 1) return true;
                else return false;
            }
            catch (Exception e)
            {

                return false;
            }
        }


        
        #endregion category



        #region member

        public void selectMember()
        {
            try
            {
              
                if (dataset.Tables["Member"] != null) dataset.Tables.Remove("Member");


                SqlDataAdapter adapter1 = new SqlDataAdapter(@"SELECT     dbo.Member.*
FROM         dbo.Member
", connection);
                adapter1.Fill(dataset, "Member");
            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=admin" + e.Message);
            }
        }

        public Boolean updateMember(int userid,int role, int status)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"UPDATE [Tuvan].[dbo].[Member]
 SET 
[Role]=@role,      
[Status] = @status
     
 WHERE UserID=@userid";


                cmd.Parameters.Add("@userid", SqlDbType.Int).Value = userid;
                cmd.Parameters.Add("@role", SqlDbType.SmallInt).Value = role;
                cmd.Parameters.Add("@status", SqlDbType.SmallInt).Value = status;



                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                if (k == 1) return true;
                else return false;
            }
            catch (Exception e)
            {

                return false;
            }
        }
        
        #endregion member


        #region feedback
        public void selectFeedBack()
        {
            try
            {
                if (dataset.Tables["Feedback"] != null) dataset.Tables.Remove("Feedback");
                if (dataset.Tables["Member"] != null) dataset.Tables.Remove("Member");

                SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT     dbo.Session.*
FROM         dbo.Session
WHERE     (SessionPayment = 0) AND  (ToID =" + HttpContext.Current.Session["UserID"].ToString() + ") AND (SessionStatus = 1)", connection);
                adapter.Fill(dataset, "Feedback");

                SqlDataAdapter adapter1 = new SqlDataAdapter(@"SELECT dbo.Member.* FROM  dbo.Member", connection);
                adapter1.Fill(dataset, "Member");
            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=admin" + e.Message);
            }
        }
        #endregion feedback



        #region Card
        public void selectCard()
        {
            try
            {
                if (dataset.Tables["Card"] != null) dataset.Tables.Remove("Card");

                SqlDataAdapter adapter = new SqlDataAdapter(@"SELECT [CardID]
      ,[CardSerial]
      ,[CardValue]
      ,[CardStatus]
  FROM [Tuvan].[dbo].[Card]", connection);
                adapter.Fill(dataset, "Card");
            }
            catch (Exception e)
            {

                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=selectCard");
            }
        }


        public bool addCard(String cardserial, decimal money)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"INSERT INTO [Tuvan].[dbo].[Card]
         
           ([CardSerial]
           ,[CardValue]
           ,[CardStatus]  )
          
     VALUES
           (@cardserial
           ,@money
            ,1
          )";

                cmd.Parameters.Add("@cardserial", SqlDbType.VarChar, 16).Value = cardserial;
                /*co the chinh so luong cho content*/
                cmd.Parameters.Add("@money", SqlDbType.Money).Value = money;
                // cmd.Parameters.Add("@time", SqlDbType.DateTime).Value = DateTime.Now.ToString();




                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                if (k == 1) return true;
                else return false;
            }
            catch (Exception adde)
            {

                return false;
            }

        }


        public bool deleteCard(int cardid)
        {
            try
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = @"DELETE FROM [Tuvan].[dbo].[Card]
      WHERE CardID=@ID";

                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = cardid;
                /*co the chinh so luong cho content*/




                int k = cmd.ExecuteNonQuery();
                connection.Close();
                cmd.Dispose();
                return true;


            }
            catch (Exception deletee)
            {


                HttpContext.Current.Response.Redirect("adminthongbao.aspx?id=deletenews");
                return false;

            }

        }

        #endregion Card

    }
}